package com.example.demo.bean;

import com.example.demo.core.enums.ElasticTypeEnum;
import org.apache.commons.lang.NullArgumentException;
import org.springframework.util.StringUtils;

import java.text.ParseException;

public class BuildNewHisSql {

    public static String getSql(String theme, String timestamps, int limit) throws ParseException {
        if (StringUtils.isEmpty(theme)) {
            throw new NullArgumentException("theme");
        }

        ElasticTypeEnum typeEnum = ElasticTypeEnum.getByTheme(theme);
        if (typeEnum == null) {
            throw new NullPointerException(theme + " 找不到指定ES类型");
        }
        String sql = null;
        switch (typeEnum) {
            case MEDICAL_RECORD:
                sql = getAdmSql(timestamps, limit);
                break;
            case DIAGNOSE:
                sql = getDiagnoseSql(timestamps, limit);
                break;
            case ORDITEM:
                sql = getOrdItemSql(timestamps, limit);
                break;
            case OrdRis:
                sql = getOrdRisSql(timestamps, limit);
                break;
            case Lisitem:
                sql = getLisitemSql(timestamps, limit);
                break;
            case Operation:
                sql = getOperation(timestamps, limit);
                break;
            default:
                break;
        }

        return sql;
    }

    /**
     * 获取新HIS库抽取就诊信息的SQL
     * @param time
     * @param limit
     * @return
     * @throws ParseException
     */
    private static String getAdmSql(String time, int limit) throws ParseException {
        String sql = "SELECT pa_adm.paadm_rowid AS mr_admid, \n" +
                "  pa_patmas.papmi_no AS mr_regno, \n" +
                "  pa_patmas.papmi_dob AS mr_birthday, \n" +
                "  pa_adm.paadm_hospital_dr AS mr_admhospital_code, \n" +
                "  pa_adm.paadm_depcode_dr AS mr_admdept_code,\n" +
                "  pa_adm.paadm_type AS mr_admtype_code, \n" +
                "  pa_adm.paadm_admdate AS mr_admdate, \n" +
                "  pa_adm.paadm_admtime AS mr_admtime, \n" +
                "  pa_adm.paadm_dischgdate AS mr_dischdate, \n" +
                "  pa_adm.paadm_dischgtime AS mr_dischtime,\n" +
                "  pa_adm.paadm_dispos_dr AS mr_dischdept_code, \n" +
                "  pa_adm.paadm_visitstatus AS mr_visitstatus_code, \n" +
                "  pa_adm.t_timestamp AS adm_timestamp\n" +
                " FROM db002_dsn0001_sqluser.pa_adm pa_adm\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.pa_patmas pa_patmas \n" +
                "\tON pa_patmas.papmi_rowid = pa_adm.paadm_papmi_dr ";

        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
                .append(BuildBaseSql.getWhere("pa_adm.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    /**
     * 获取诊断sql
     * @param time
     * @param limit
     * @return
     */
    private static String getDiagnoseSql(String time, int limit) throws ParseException {
        String sql = "SELECT \n" +
                " mr_diagnos.mrdia_rowid AS diag_id, \n" +
                " pa_adm.paadm_rowid AS diag_admid, \n" +
                " pa_patmas.papmi_no AS diag_regno, \n" +
                " mr_diagnos.mrdia_date AS diag_date, \n" +
                " mr_diagnos.mrdia_time AS diag_time, \n" +
                " mr_diagtype.typ_mrcdiagtyp AS diag_type_code, \n" +
                " mr_diagnos.mrdia_desc AS diag_name, \n" +
                " mr_diagnos.mrdia_icdcode_dr AS diag_code, \n" +
                " mr_diagnos.t_timestamp AS diag_timestamp\n" +
                "FROM db002_dsn0001_sqluser.mr_diagnos mr_diagnos\n" +
                "\tINNER JOIN db002_dsn0001_sqluser.mr_adm mr_adm ON mr_adm.mradm_rowid = mr_diagnos.mrdia_mradm_parref\n" +
                "\tINNER JOIN db002_dsn0001_sqluser.pa_adm pa_adm ON pa_adm.paadm_mainmradm_dr = mr_adm.mradm_rowid\n" +
                "\tINNER JOIN db002_dsn0001_sqluser.pa_patmas pa_patmas ON pa_patmas.papmi_rowid = pa_adm.paadm_papmi_dr\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.mr_diagtype mr_diagtype \n" +
                "\tON mr_diagtype.typ_parref = mr_diagnos.mrdia_rowid";
        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
                .append(BuildBaseSql.getWhere("mr_diagnos.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    /**
     * 获取新HIS库抽取医嘱信息的SQL
     * @param time
     * @param limit
     * @return
     * @throws ParseException
     */
    private static String getOrdItemSql(String time, int limit) throws ParseException {
        String sql = "SELECT \n" +
                " oe_orditem.oeori_rowid AS ord_id, \n" +
                " pa_adm.paadm_rowid AS ord_admno, \n" +
                " pa_patmas.papmi_no AS ord_regno, \n" +
                " oe_orditem.oeori_itmmast_dr AS ord_code, \n" +
                " oe_orditem.oeori_opercategory_dr AS ord_category_code,\n" +
                " oe_orditem.oeori_priority_dr AS ord_type_code, \n" +
                " oe_orditem.oeori_durat_dr AS ord_duration_code, \n" +
                " oe_orditem.oeori_instr_dr AS ord_usage_code, \n" +
                " oe_orditem.oeori_phfreq_dr AS ord_freq_code, \n" +
                " oe_orditem.oeori_sttdat AS ord_startdate, \n" +
                " oe_orditem.oeori_stttim AS ord_starttime, \n" +
                " oe_orditem.oeori_itemstat_dr AS ord_status_code, \n" +
                " oe_orditem.oeori_phqtyord AS ord_orderqty, \n" +
                " oe_orditem.oeori_enddate AS ord_enddate, \n" +
                " oe_orditem.oeori_endtime AS ord_endtime, \n" +
                " oe_orditem.oeori_orddept_dr AS ord_dept_code, \n" +
                " oe_orditem.oeori_dateexecuted AS ord_dateexecuted, \n" +
                " oe_orditem.oeori_doseqty AS ord_doseqty, \n" +
                " oe_orditem.t_timestamp AS ord_timestamp\n" +
                " FROM db002_dsn0001_sqluser.oe_orditem oe_orditem\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.oe_order oe_order ON oe_order.oeord_rowid = oe_orditem.oeori_oeord_parref\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.pa_adm pa_adm ON pa_adm.paadm_rowid = oe_order.oeord_adm_dr\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.pa_patmas pa_patmas ON pa_patmas.papmi_rowid = pa_adm.paadm_papmi_dr \n" +
                " ";

        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
                .append(BuildBaseSql.getWhere("oe_orditem.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    private static String getOrdRisSql(String time, int limit) throws ParseException {
        String sql = " SELECT multimedia_info.record_no AS or_id, " +
                " pa_adm.paadm_rowid AS ord_admno," +
                " pa_patmas.papmi_no AS ord_regno," +
                " multimedia_info.report_date AS or_date," +
                " register_info.body_of_case AS or_name\n" +
                "\t, oe_orditem.oeori_itmmast_dr AS or_ordcode," +
                " multimedia_info.report_desc_txt AS or_desc," +
                " multimedia_info.report_diag_txt AS or_result," +
                " multimedia_info.ods_updatetime AS ods_updatetime," +
                " multimedia_info.t_timestamp AS ris_timestamp\n" +
                "FROM db002_dsn0006_pacs3i.view_multimedia_info multimedia_info\n" +
                "\tINNER JOIN db002_dsn0006_pacs3i.view_register_info register_info ON register_info.record_no = multimedia_info.record_no\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.oe_orditem oe_orditem ON oe_orditem.oeori_rowid = replace(register_info.his_uid, '_', '||')\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.oe_order oe_order ON oe_order.oeord_rowid = oe_orditem.oeori_oeord_parref\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.pa_adm pa_adm ON pa_adm.paadm_rowid = oe_order.oeord_adm_dr\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.pa_patmas pa_patmas ON pa_patmas.papmi_rowid = pa_adm.paadm_papmi_dr\n" +
                "WHERE char_length(register_info.his_uid) > 0 and char_length(pa_adm.paadm_rowid) > 0";

        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
                .append(BuildBaseSql.getWhere("multimedia_info.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    private static String getLisitemSql(String time, int limit) throws ParseException {
        String sql = "SELECT \n" +
                "\tlis_inspection_result.lis_id AS lis_id, \n" +
                "\tlis_inspection_result.test_item_id AS test_item_id, \n" +
                "\tlis_inspection_result.group_id AS group_id, \n" +
                "\tlis_inspection_result.inspection_date AS lis_date,\n" +
                "\tlis_inspection_result.inspection_time AS lis_time,\n" +
                "\tlis_inspection_result.sample_number AS lis_sample_number, \n" +
                "\tlis_inspection_result.english_name AS lis_code,\n" +
                "\tlis_inspection_result.chinese_name AS lis_name, \n" +
                "\tlis_inspection_result.quantitative_result AS lis_value,\n" +
                "\tlis_inspection_result.qualitative_result AS lis_result,\n" +
                "\tlis_inspection_result.test_item_unit AS lis_unit, \n" +
                "\tlis_inspection_result.dhcchis_id AS his_id,\n" +
                "\toe_orditem.oeori_rowid AS oeori_rowid, \n" +
                "\toe_orditem.oeori_itmmast_dr AS lis_ordcode, \n" +
                "\toe_orditem.oeori_itmmast_dr || '_' || lis_inspection_result.english_name AS lis_searchcode, \n" +
                "\toe_order.oeord_date AS oeord_date, \n" +
                "\tpa_adm.paadm_rowid AS lis_admno, \n" +
                "\tpa_patmas.papmi_no AS lis_regno,\n" +
                "\tlis_inspection_result.t_timestamp AS at_timestamp\n" +
                "FROM (\n" +
                "\tSELECT a.inspection_id || a.test_item_id AS lis_id\n" +
                "\t\t, replace(a.his_id, '@', '||') AS dhcchis_id\n" +
                "\t\t, *\n" +
                "\tFROM db002_dsn0006_lis_dbo.lis_inspection_result a\n" +
                ") lis_inspection_result\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.oe_orditem oe_orditem ON oe_orditem.oeori_rowid = lis_inspection_result.dhcchis_id\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.oe_order oe_order ON oe_order.oeord_rowid = oe_orditem.oeori_oeord_parref\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.pa_adm pa_adm ON pa_adm.paadm_rowid = oe_order.oeord_adm_dr\n" +
                "\tLEFT JOIN db002_dsn0001_sqluser.pa_patmas pa_patmas ON pa_patmas.papmi_rowid = pa_adm.paadm_papmi_dr\n" +
                " where char_length(pa_patmas.papmi_no) > 0\n" +
                "\tAND char_length(pa_adm.paadm_rowid) > 0";
        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
                .append(BuildBaseSql.getWhere("lis_inspection_result.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    private static String getOperation(String time, int limit) throws ParseException {
        String sql = "select tmrdde.fid as admno,pa_patmas.papmi_no as regno,\n" +
                "tmrdde.fmrdid as oper_id, \n" +
                "\ttmrdop.fopid as oper_code,\n" +
                "\t tmrdop.fopdoc as operator,\n" +
                "\t tmrdop.fopfz1 as opera_assistant_fir,\n" +
                "\t tmrdop.fopfz2 as opera_assistant_sec,\n" +
                "\t tmrdop.fopdat as oper_date,\n" +
                "\t tmrdop.fyhtyp as op_type, \n" +
                "\t topsm.fdesc as oper_name,\n" +
                "\t thocus.fdesc as anaes_way,\n" +
                "\t tmrdop.fmzys as anaes_doc,\n" +
                "\t tcutm.fdesc as ncision_healing_level, \n" +
                "\t tmrdop.t_timestamp  \n" +
                "\t from db002_dsn0006_mhis.tmrdop \n" +
                "\t\tleft join db002_dsn0006_mhis.tmrdde \n" +
                "\t\t on tmrdde.fmrdid = tmrdop.fmrdid  \n" +
                "\t\t left join db002_dsn0006_mhis.topsm \n" +
                "\t\t on  topsm.fid = tmrdop.fopid \n" +
                "\t\t left join db002_dsn0006_mhis.thocus \n" +
                "\t\t on thocus.fid = tmrdop.fmzth  \n" +
                "\t\t left join db002_dsn0006_mhis.tcutm \n" +
                "\t\t  on tcutm.fid = tmrdop.fcut \n" +
                "\t\t  LEFT JOIN db002_dsn0001_sqluser.pa_patmas pa_patmas \n" +
                "\t      ON pa_patmas.papmi_rowid = tmrdde.fid\n" +
                "\t\t where tmrdde.fid is not null  ";
        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
//                .append(BuildBaseSql.getWhere("tmrdop.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    public static void main(String[] args) throws ParseException {
        String timestemp = "2019-11-11";

        System.out.println("adm sql: " + getAdmSql(timestemp, 10));

        System.out.println("diagnose sql: " + getDiagnoseSql(timestemp, 10));

        System.out.println("ordris sql: " + getOrdRisSql(timestemp, 10));

        System.out.println("lisitem sql " + getLisitemSql(timestemp, 10));
    }
}
